# -*- coding: UTF-8 -*-
# Author: Damon(wuud1)
# CreateDate: 2018-07-23
# Message: 通过从数据库中读取出来的零件pid去mann(曼牌)网站上查询,获取需要的数据(对应的曼牌生产的零件号,及对应的其他车型的零件号)
# Tips: 程序执行之前,需要修改每个headers字典里的cookies字段, post_data字典里的j_idt**字段(如果获取不到最终数据的话,需要查看前一个请求获取的源码里找j_idt**)
        # 数据库配置及sql语句也需要响应的修改, 本网站遇到的困难: j_idt字段会变化,例如:中午时是j_idt331, 下午重新运行获取不到数据,发现变成了j_idt337
# Python Version: 2.7.1

import re
import requests
import sys
from collections import OrderedDict
import json
import time
import os
import hashlib
import pymysql
from lxml import etree

# 解决编码问题
reload(sys)
sys.setdefaultencoding('utf-8')

class Mann_For_Pid(object):

    def __init__(self, dbconfig):
        self.base_path = '/Users/duanmengdi/Desktop/workspace/mann_html/'  # 存储html文件的路径
        self.base_url = 'https://catalog.mann-filter.cn'
        self.total_num = 0
        self.db_config = dbconfig
        self.start_url = "https://catalog.mann-filter.cn/CN/chi/filter"
        self.javax_value = ""  # 用来保存首页的隐藏域的值
        # 第一次post请求的参数字典
        self.first_post_data = OrderedDict()
        self.first_post_data['headerSearchForm:headerSearchForm'] = 'headerSearchForm:headerSearchForm'
        self.first_post_data['headerSearchForm:headerSearchForm:searchQueryInput'] = ''  # 搜索的pid
        self.first_post_data['javax.faces.ViewState'] = ''  # 隐藏域的值,需要动态获取
        self.first_post_data['javax.faces.source'] = 'headerSearchForm:headerSearchForm:j_idt121'
        self.first_post_data['javax.faces.partial.execute'] = 'headerSearchForm:headerSearchForm:j_idt121 @component'
        self.first_post_data['javax.faces.partial.render'] = '@component'
        self.first_post_data['searchQuery'] = ''  # 搜索的pid
        self.first_post_data['org.richfaces.ajax.component'] = 'headerSearchForm:headerSearchForm:j_idt121'
        self.first_post_data['headerSearchForm:headerSearchForm:j_idt121'] = 'headerSearchForm:headerSearchForm:j_idt121'
        self.first_post_data['rfExt'] = 'null'
        self.first_post_data['AJAX:EVENTS_COUNT'] = '1'
        self.first_post_data['javax.faces.partial.ajax'] = 'true'

        self.first_headers = {
            "Host": "catalog.mann-filter.cn",
            "Upgrade-Insecure-Requests": "1",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36",
            "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8",
            "Accept-Encoding": "gzip, deflate, br",
            "Accept-Language": "zh-CN,zh;q=0.9",
            "Cookie": "anonymousid=8eae8bae-c418-4661-ad61-4ebe69f97187; JSESSIONID=A88E74F6E6CCAB815EB4A81AD269D5BA; csfcfc=W%2Ft9d3qy",
            "Connection": "keep-alive",
            }

        # 第一次post请求时的headers, 点击搜索,请求重定向标签
        self.first_post_headers = {
            "Host": "catalog.mann-filter.cn",
            "Connection": "keep-alive",
            "Faces-Request": "partial/ajax",
            "Origin": "https://catalog.mann-filter.cn",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36",
            "Content-type": "application/x-www-form-urlencoded;charset=UTF-8",
            "Accept": "*/*",
            "Referer": "https://catalog.mann-filter.cn/CN/chi/filter",
            "Accept-Encoding": "gzip, deflate, br",
            "Accept-Language": "zh-CN,zh;q=0.9",
            "Cookie": "anonymousid=8eae8bae-c418-4661-ad61-4ebe69f97187; JSESSIONID=A88E74F6E6CCAB815EB4A81AD269D5BA; csfcfc=W%2Ft9d3qy"}

        # 第二次post请求的data, 请求对照表界面
        self.second_post_data = OrderedDict()
        self.second_post_data['productDetail'] = 'productDetail'
        self.second_post_data['productDetail:productDetailTabPanel-value'] = 'productDetail:productDetailCompareTab'
        self.second_post_data['javax.faces.ViewState'] = ''
        self.second_post_data['javax.faces.source'] = 'productDetail:productDetailCompareTab'
        self.second_post_data['javax.faces.partial.execute'] = 'productDetail:productDetailCompareTab @component'
        self.second_post_data['javax.faces.partial.render'] = '@component'
        self.second_post_data['org.richfaces.ajax.component'] = 'productDetail:productDetailCompareTab'
        self.second_post_data['productDetail:productDetailCompareTab'] = 'productDetail:productDetailCompareTab'
        self.second_post_data['rfExt'] = 'null'
        self.second_post_data['AJAX:EVENTS_COUNT'] = '1'
        self.second_post_data['javax.faces.partial.ajax'] = 'true'

        # 第二次post请求的headers
        self.second_post_headers = {
            "Host": "catalog.mann-filter.cn",
            "Connection": "keep-alive",
            "Faces-Request": "partial/ajax",
            "Origin": "https://catalog.mann-filter.cn",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36",
            "Accept": "*/*",
            "Referer": "",
            "Accept-Encoding": "gzip, deflate, br",
            "Accept-Language": "zh-CN,zh;q=0.9",
            "Cookie": "anonymousid=8eae8bae-c418-4661-ad61-4ebe69f97187; JSESSIONID=A88E74F6E6CCAB815EB4A81AD269D5BA; csfcfc=W%2Ft9d3qy",
            "Content-Type": "application/x-www-form-urlencoded;charset=UTF-8",
        }

        # 最后一次的post请求,获取最终数据的post_data
        self.last_post_data = OrderedDict()
        self.last_post_data['productDetail'] = 'productDetail'
        self.last_post_data['productDetail'] = 'productDetailTabPanel-value'
        self.last_post_data['productDetail'] = 'productDetailCompareTab'
        self.last_post_data['javax.faces.ViewState'] = ''
        self.last_post_data['javax.faces.source'] = 'productDetail:j_idt337'
        self.last_post_data['javax.faces.partial.execute'] = 'productDetail:j_idt337 @component'
        self.last_post_data['javax.faces.partial.render'] = '@component'
        self.last_post_data['name'] = ''
        self.last_post_data['org.richfaces.ajax.component'] = 'productDetail:j_idt337'
        self.last_post_data['productDetail:j_idt337'] = 'productDetail:j_idt337'
        self.last_post_data['rfExt'] = 'null'
        self.last_post_data['AJAX:EVENTS_COUNT'] = '1'
        self.last_post_data['javax.faces.partial.ajax'] = 'true'
        self.last_post_data['productDetail:productDetailTabPanel-value'] = 'productDetail:productDetailCompareTab'

    def getmd5(self, str):
        md5 = hashlib.md5()
        md5.update(str)
        return md5.hexdigest()

    def get_conn(self, dbconfig_dict):
        conn = pymysql.connect(**dbconfig_dict)
        return conn

    def get_cursor(self, conn, type='stream'):
        if type == 'stream':
            return conn.cursor(pymysql.cursors.SSCursor)  # 返回流式游标,查询大量数据时不占用内存(返回数据形式是元组)
        elif type == 'dict':
            return conn.cursor(pymysql.cursors.DictCursor)  # 返回字典形式游标,查询出的数据以字典形式返回
        else:
            raise Exception("cursor type error")

    def parse_url(self, url, method, post_data=None, headers=None, tofile=True, **kwargs):
        requests.packages.urllib3.disable_warnings()  # 因为把ssl安全验证关了,所以不让代码报不安全的https请求警告
        pid = kwargs.get("local_pid", "")
        if method == 'GET':
            data = "GET" + url + pid  # 拼接data
            md5 = self.getmd5(data)  # 获取此次请求电子指纹
            file_path = self.base_path + md5 + ".html"
            if os.path.exists(file_path):  # 判断文件是否存在,如果存在就从本地读取
                print "GET for local"
                with open(file_path, 'r') as f:
                    res = f.read()
                response = res
            else:  # 如果路径不存在, 从web端请求
                print "GET for web"
                try:
                    response = requests.get(url=url, verify=False, headers=headers, timeout=30,
                                            allow_redirects=False)
                except Exception as e:
                    response = requests.get(url=url, verify=False, headers=headers, timeout=30,
                                            allow_redirects=False)

                if response:
                    if tofile:
                        with open(file_path, 'w') as f:  # 保存此次请求的html
                            f.write(response.content.decode('utf8'))
                    response = response.content.decode("utf8")
                else:
                    response = None

        elif method == "POST":
            data = "POST" + url + json.dumps(post_data)
            md5 = self.getmd5(data)
            file_path = self.base_path + md5 + ".html"
            if os.path.exists(file_path):  # 判断文件是否存在,如果存在就从本地读取
                print "POST for local"
                with open(file_path, 'r') as f:
                    res = f.read()
                response = res
            else:
                print "POST for web"
                try:
                    response = requests.post(url=url, data=post_data, verify=False, headers=headers, timeout=30,
                                             allow_redirects=False)
                except Exception as e:
                    response = requests.post(url=url, data=post_data, verify=False, headers=headers, timeout=30,
                                             allow_redirects=False)

                if response:
                    # cookies = response.cookies.get_dict()  # 可以在此获取cookies
                    # print cookies
                    if tofile:
                        with open(file_path, 'w') as f:  # 保存此次请求的html
                            f.write(response.content.decode('utf8'))
                    response = response.content.decode("utf8")

                else:
                    response = None

        return response

    def save2db(self, data_dict, curs):
        # 抽取数据
        pid = data_dict['pid'].decode('utf8')
        brand_name = data_dict['brand_name'].decode('utf8')
        part_brand = data_dict['part_brand'].decode('utf8')
        part_num = data_dict['part_num'].decode('utf8')
        part_name = data_dict['part_name'].decode('utf8')
        brand_name1 = data_dict['brand_name1'].decode('utf8')
        pid1 = data_dict['pid1'].decode('utf8')
        status = data_dict['status'].decode('utf8')

        # 拼接sql语句
        sql = '''INSERT INTO `mann_` (`pid`, `brand_name`, `part_brand`, `part_num`, `part_name`, `brand_name1`, `pid1`, `status`) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')''' % (pid, brand_name, part_brand, part_num, part_name, brand_name1, pid1, status)
        try:
            curs.execute(sql)
        except Exception as e:
            print e
            print sql
            exit()
        self.total_num += 1  # 数据总量计数

    def run(self):
        # 首先从数据库读取pid出来
        # 首先获取数据库链接和游标
        conn1 = self.get_conn(dbconfig_dict=dbconfig)
        conn2 = self.get_conn(dbconfig_dict=dbconfig)  # 用于存储
        curs1 = self.get_cursor(conn=conn1)
        curs2 = self.get_cursor(conn=conn2)  # 用于存储

        # 查询语句
        sql = u'''select brand,item,pid from 0719_pid'''
        curs1.execute(sql)
        for data in curs1:
            time.sleep(1)  # 控制请求web的速度,防止被封,第二遍从本地请求的时候可以屏蔽
            local_pid = data[2]
            # local_pid = 'LR011279'
            brand_name = data[0]

            # 第一次搜索请求是个post请求,一些参数从首页的隐藏域标签中获取,
            # 响应是个重定向标签,包含了重定向的网站,下一步直接get请求该网站就ok
            # 因为界面隐藏域内容会变化,所以请求首页不保存本地  tofile=False
            index_response = self.parse_url(url=self.start_url, method="GET", headers=self.first_headers, local_pid=local_pid)
            if index_response:
                index_html = etree.HTML(index_response)
            else:
                raise Exception("index_response is None type")
            # 开始获取隐藏域的参数
            # self.javax_value = index_html.xpath('//input[@id="j_id1:javax.faces.ViewState:0"]/@value')
            self.javax_value = index_html.xpath('//input[contains(@id,"javax.faces.ViewState")]/@value')
            self.javax_value = self.javax_value[0] if len(self.javax_value)>0 else ""
            # print self.javax_value
            # 拼接搜索请求时的第一次post请求参数
            self.first_post_data['javax.faces.ViewState'] = self.javax_value
            self.first_post_data['searchQuery'] = local_pid  # 搜索的pid
            #
            search_redirect_response = self.parse_url(url=self.start_url, method="POST", post_data=self.first_post_data, headers=self.first_post_headers, local_pid=local_pid)
            if search_redirect_response:
                redirect_html = etree.HTML(bytes(search_redirect_response))
            else:
                raise Exception("search_redirect_response is None type")

            redirect_url = redirect_html.xpath(u'//redirect/@url')
            redirect_url = self.base_url + redirect_url[0] if len(redirect_url)>0 else ""
            if redirect_url == "":
                print "没有响应redirect_url, 跳过", local_pid
                continue
            # print local_pid
            # print redirect_url

            # 获取到搜索结果的重定向url, get请求, first_headers可以使用
            search_response = self.parse_url(url=redirect_url, method="GET", headers=self.first_headers, local_pid=local_pid)
            if search_response:
                search_html = etree.HTML(search_response)
            else:
                raise Exception("search_response is None type")

            detail_url_list = search_html.xpath('//div[@class="tableContent"]/a/@href')
            part_num = search_html.xpath('//div[@class="tableContent"]/a/text()')
            part_num = part_num[0].replace(' ', '').replace('\t', '') if len(part_num)>0 else ""

            if len(detail_url_list) <= 0:
                print "未找到详情页url, 跳过", local_pid
                continue

            for detail_url in detail_url_list:

                detail_url = self.base_url+detail_url if detail_url != "" else ""
                print detail_url
                if detail_url == "":
                    continue
                detail_response = self.parse_url(url=detail_url, method="GET", headers=self.first_headers, local_pid=local_pid)
                if detail_response:
                    detail_html = etree.HTML(detail_response)
                else:
                    raise Exception("detail_response is None type")

                part_name = detail_html.xpath('//span[@id="productDetail:productName"]/text()')
                part_name = part_name[0].strip().replace(' ', '') if len(part_name) else ""
                status = detail_html.xpath('//ul[@class="productInformation"]/li[3]/span/text()')
                status = status[0].strip().replace(' ', '') if len(status)>0 else ""

                # 为下次进入对照表界面请求做准备,获取javax_value并更新到
                self.javax_value = detail_html.xpath('//input[contains(@id,"javax.faces.ViewState")]/@value')
                self.javax_value = self.javax_value[0] if len(self.javax_value) > 0 else ""

                # 拼装数据和headers头
                self.second_post_data['javax.faces.ViewState'] = self.javax_value
                self.second_post_headers['Referer'] = detail_url
                # print self.second_post_headers['Referer']
                duizhao_response = self.parse_url(url=detail_url, method="POST", post_data=self.second_post_data, headers=self.second_post_headers, local_pid=local_pid)
                if duizhao_response:
                    duizhao_html = etree.HTML(bytes(duizhao_response))
                else:
                    raise Exception("duizhao_response is None type")

                # 获取最后一次请求所需的必要参数
                self.javax_value = re.findall('ViewState:0"><!\[CDATA\[(.*?)\]\]>', duizhao_response)
                self.javax_value = self.javax_value[0] if len(self.javax_value)>0 else ""
                # print self.javax_value

                name_list = duizhao_html.xpath('//span[@class="label_lv1"]/text()')

                if len(name_list) <= 0:
                    print "未找到车名列表 跳过", local_pid
                    continue

                for name in name_list:
                    # print name
                    brand_name1 = name.replace(' ', '')

                    # 拼装最后一次的postdata
                    self.last_post_data['name'] = name
                    self.last_post_data['javax.faces.ViewState'] = self.javax_value

                    # 请求最后一次
                    last_response = self.parse_url(url=detail_url, method="POST", post_data=self.last_post_data, headers=self.second_post_headers, local_pid=local_pid)
                    if last_response:
                        last_html = etree.HTML(bytes(last_response))
                    else:
                        raise Exception("last_response is None type")

                    pid1_list = last_html.xpath('//ul[@class="basic_list"]/li')
                    if len(pid1_list) <= 0:
                        print "没有找到pid1列表, 跳过", local_pid
                        continue

                    for pid1 in pid1_list:
                        pid1 = pid1.xpath('./text()')
                        pid1 = pid1[0].strip().replace(' ', '').replace('-', '') if len(pid1)>0 else ""

                        # 根据pid1 过滤数据
                        if pid1 == local_pid:
                            print "pid1=local_pid, 跳过", pid1, local_pid
                            continue
                        elif pid1 == "":
                            print "pid1 = '', 跳过", local_pid
                            continue
                        # 组装最后数据字典
                        last_dict = dict(
                            pid=local_pid,
                            brand_name=brand_name,
                            part_brand='mann',  # 爬的是hengest的网站,品牌当然是hengest
                            part_num=part_num.replace('\n', ''),
                            part_name=part_name,
                            brand_name1=brand_name1,
                            pid1=pid1,
                            status=status
                        )
                        print last_dict

                        self.save2db(data_dict=last_dict, curs=curs2)

                        if self.total_num % 100 == 0:
                            conn2.commit()
                            print '>>mysql提交<<'

        conn2.commit()  # 存储数据提交
        print self.total_num
        # 关闭sql链接,释放内存
        curs1.close()
        curs2.close()
        conn1.close()
        conn2.close()

if __name__ == '__main__':
    dbconfig = dict(
        host="127.0.0.1",
        port=3306,
        user="root",
        passwd="123456",
        db="my_data",
        charset="utf8"
    )
    tools = Mann_For_Pid(dbconfig=dbconfig)
    tools.run()






